package com.xlz.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.xlz.util.ReflectionUtils;
import com.xlz.util.StringUtils;

/**
 * 实例信息CRUD基础工具类.
 * @author 张蕾蕾
 * @date 2018 03 24
 */
public abstract class BaseJdbcService<T> {
	protected final Logger LOG = LoggerFactory.getLogger(getClass());

	private DataSource dataSource;
	protected Map<String, String> fieldMapping = new HashMap<String, String>();

	public BaseJdbcService(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public abstract String getNamespace();

	public int executeUpdate(String sql) throws SQLException{
		Connection connection = null;
		PreparedStatement pstmt = null;
		int count = 0;
		try {
			connection = this.dataSource.getConnection();
			pstmt = connection.prepareStatement(sql);
			count = pstmt.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			if (connection != null)
				try {
					connection.rollback();
				} catch (SQLException e1) {
					LOG.error("执行sql回滚时存在异常", sql, e1);
				}
			LOG.error("执行sql存在异常", sql, e);
			throw e;
		}finally{
			closeResources( connection,  pstmt);
		}
		return count;
	}

	public int[] executeBatchUpdate(List<String> sqls) {
		Connection connection = null;
		Statement pstmt = null;
		int count[] = new int[0];
		try {
			connection = this.dataSource.getConnection();
			pstmt = connection.createStatement();
			for (String sql : sqls) {
				pstmt.addBatch(sql);
			}
			count = pstmt.executeBatch();
			connection.commit();
		} catch (SQLException e) {
			if (connection != null)
				try {
					connection.rollback();
				} catch (SQLException e1) {
					LOG.error("执行批量sql回滚时存在异常", sqls, e1);
				}
			LOG.error("执行批量sql存在异常", sqls, e);
		}finally{
			closeResources( connection,  pstmt);
		}

		return count;
	}

	public T find(String sql, Class<T> cls) throws Exception {
		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet resultSet = null;
		T resultObject = null;
		try {
			connection = this.dataSource.getConnection();
			pstmt = connection.prepareStatement(sql);
			resultSet = pstmt.executeQuery();
			ResultSetMetaData metaData = resultSet.getMetaData();
			int cols_len = metaData.getColumnCount();
			
			while (resultSet.next()) {
				// 通过反射机制创建一个实例
				resultObject = cls.newInstance();
				for (int i = 0; i < cols_len; i++) {
					// String tableName = resultSet.getta;
					String cols_name = metaData.getColumnName(i + 1);
					Object cols_value = resultSet.getObject(cols_name);
					if (cols_value == null) {
						cols_value = null;
					}
					String objectField = fieldMapping.get(getNamespace() + "cols_name");
					if (objectField == null) {
						objectField = StringUtils.underlineToCamel(cols_name);
						fieldMapping.put(getNamespace() + cols_name, objectField);
					}
					ReflectionUtils.setFieldValue(resultObject, objectField, cols_value);
				}
			}
		} catch (SQLException e) {
			LOG.error("执行批量sql存在异常", sql, e);
		} catch (InstantiationException e) {
			LOG.error("查询结果生成对象存在异常", sql, e);
		} catch (IllegalAccessException e) {
			LOG.error("查询结果生成对象存在异常", sql, e);
		}finally{
			closeResources( connection,  pstmt,resultSet);
		}
		return resultObject;
	}
	
	public int findCount(String sql) throws Exception {
		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet resultSet = null;
		try {
			connection = this.dataSource.getConnection();
			pstmt = connection.prepareStatement(sql);
			resultSet = pstmt.executeQuery();
			while (resultSet.next()) {
				return resultSet.getInt(1);
			}
		} catch (SQLException e) {
			LOG.error("执行批量sql存在异常", sql, e);
		}finally{
			closeResources( connection,  pstmt,resultSet);
		}
		return 0;
	}

	public List<T> findAll(String sql, Class<T> cls) {
		List<T> list = new ArrayList<T>();
		Connection connection = null;
		PreparedStatement pstmt= null;
		ResultSet resultSet = null;
		try {
			connection = this.dataSource.getConnection();
			pstmt = connection.prepareStatement(sql);
			resultSet = pstmt.executeQuery();
			ResultSetMetaData metaData = resultSet.getMetaData();
			int cols_len = metaData.getColumnCount();
			while (resultSet.next()) {
				// 通过反射机制创建一个实例
				T resultObject = cls.newInstance();
				for (int i = 0; i < cols_len; i++) {
					String cols_name = metaData.getColumnName(i + 1);
					Object cols_value = resultSet.getObject(cols_name);
					if (cols_value == null) {
						cols_value = null;
					}
					String objectField = fieldMapping.get(getNamespace() + "cols_name");
					if (objectField == null) {
						objectField = StringUtils.underlineToCamel(cols_name);
						fieldMapping.put(getNamespace() + cols_name, objectField);
					}
					ReflectionUtils.setFieldValue(resultObject, objectField, cols_value);
				}
				list.add(resultObject);
			}
		} catch (SQLException e) {
			LOG.error("执行批量sql存在异常", sql, e);
		} catch (InstantiationException e) {
			LOG.error("查询结果生成对象存在异常", sql, e);
		} catch (IllegalAccessException e) {
			LOG.error("查询结果生成对象存在异常", sql, e);
		}finally{
			closeResources( connection,  pstmt,resultSet);
		}
		return list;
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	/** 
     * 关闭资源 
     * @param conn 
     * @param pstmt 
     * @param rs 
     */  
    public void closeResources(Connection conn, PreparedStatement pstmt, ResultSet rs) {  
        if (null != rs) {  
            try {  
                rs.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
                throw new RuntimeException(e);  
            } finally {  
                if (null != pstmt) {  
                    try {  
                        pstmt.close();  
                    } catch (SQLException e) {  
                        e.printStackTrace();  
                        throw new RuntimeException(e);  
                    } finally {  
                        if (null != conn) {  
                            try {  
                                conn.close();  
                            } catch (SQLException e) {  
                                e.printStackTrace();  
                                throw new RuntimeException(e);  
                            }  
                        }  
                    }  
                }  
            }  
        }  
    }  
      
    /** 
     * 关闭资源 
     * @param conn 
     * @param pstmt 
     */  
    public void closeResources(Connection conn, PreparedStatement pstmt) {  
        if (null != pstmt) {  
            try {  
                pstmt.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
                throw new RuntimeException(e);  
            } finally {  
                if (null != conn) {  
                    try {  
                        conn.close();  
                    } catch (SQLException e) {  
                        e.printStackTrace();  
                        throw new RuntimeException(e);  
                    }  
                }  
            }  
        }  
    }   
    
    /** 
     * 关闭资源 
     * @param conn 
     * @param pstmt 
     */  
    public void closeResources(Connection conn, Statement pstmt) {  
    	if (null != pstmt) {  
    		try {  
    			pstmt.close();  
    		} catch (SQLException e) {  
    			e.printStackTrace();  
    			throw new RuntimeException(e);  
    		} finally {  
    			if (null != conn) {  
    				try {  
    					conn.close();  
    				} catch (SQLException e) {  
    					e.printStackTrace();  
    					throw new RuntimeException(e);  
    				}  
    			}  
    		}  
    	}  
    }     
}

